﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Ledict.ConditionExtract.Common;
namespace Ledict.ConditionExtract.DAL
{
    public class ProcessDataSourceDAL
    {
        private string DbName;
        private string Case;
        public ProcessDataSourceDAL()
        {
            DbName = "JudgeDoc";
            Case = "Caselibrary";
        }

        #region 案例库数据

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetParaLogicByCaseId(DataSourceCondition conditon, string CaseId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"  select * from dbo.t_Case_ParagraphLogic
            where CaseId=@CaseId and ParagraphTypeId=@ParagraphTypeId");

            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@ParagraphTypeId", DbType.String, conditon.ParagraphType);
            oBase.AddInParameter(oCmd, "@CaseId", DbType.String, CaseId);


            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetDataSource(DataSourceCondition conditon)//案由ID
        {
            //           StringBuilder strSql = new StringBuilder();
            //           strSql.Append(@"
            //            select c.EW_ID as id  , c.EW_CaseId as CaseId , isnull(c.ParagraphTypeId,'150') as ParagraphTypeId, 
            //            c.EW_Content as ParagraphText   from  
            //            SYS_Express_GroupSituation   as a 
            //            join 
            //            SYS_Express_GroupSituationWordRel   as b 
            //            on a.ES_IDs=b.ES_IDs
            //            join 
            //            SYS_Express_Word_Process  as c 
            //            on b.EW_ID=c.EW_ID
            //            where substring(a.ES_ParentID,2,100) 
            //            in(
            //            select CC_ID from 
            //            dbo.SYS_CaseCause   
            //            where ','+AllParentId+',' like'%,'+@HeadwordId+',%'  and isLeafNode=1)
            //");

            //           //dt.Columns.Add("Id");
            //           //dt.Columns.Add("CaseId");
            //           //dt.Columns.Add("ParagraphTypeId");
            //           //dt.Columns.Add("ParagraphText");

            //           Database oBase = DatabaseFactory.CreateDatabase("JudgeDocData");
            //          DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString ());
            //           oBase.AddInParameter(oCmd, "@HeadwordId", DbType.String, conditon.HeadwordId);
            //           oBase.AddInParameter(oCmd, "@JudicialProcedureId", DbType.String, conditon.JudicialProcedureId);
            //           oBase.AddInParameter(oCmd, "@PropertyId", DbType.String, conditon.PropertyId);
            //           oBase.AddInParameter(oCmd, "@CaseIdStart", DbType.String, conditon.CaseIdStart);
            //           oBase.AddInParameter(oCmd, "@CaseIdEnd", DbType.String, conditon.CaseIdEnd);
            //           //oBase.AddInParameter(oCmd, "@CaseParagraphType", DbType.String, conditon.ParagraphType);
            //           oBase.AddInParameter(oCmd, "@Court", DbType.String, conditon.Court);
            //           oBase.AddInParameter(oCmd, "@Category", DbType.String, conditon.Category);

            //          DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //          if (OtB.Rows.Count > 0)
            //          {
            //              return OtB;
            //          }
            //          else
            //          {
            //              return new DataTable();
            //          }

            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetDataSource_first");
            oBase.AddInParameter(oCmd, "@HeadwordId", DbType.String, conditon.HeadwordId);
            oBase.AddInParameter(oCmd, "@JudicialProcedureId", DbType.String, conditon.JudicialProcedureId);
            oBase.AddInParameter(oCmd, "@PropertyId", DbType.String, conditon.PropertyId);
            oBase.AddInParameter(oCmd, "@CaseIdStart", DbType.String, conditon.CaseIdStart);
            oBase.AddInParameter(oCmd, "@CaseIdEnd", DbType.String, conditon.CaseIdEnd);
            oBase.AddInParameter(oCmd, "@Court", DbType.String, conditon.Court);
            oBase.AddInParameter(oCmd, "@CaseParagraphType", DbType.String, conditon.ParagraphType);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }

        public int GetDataSourceAllCount(DataSourceCondition conditon)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetDataSourceAllCount_first");
            oBase.AddInParameter(oCmd, "@HeadwordId", DbType.String, conditon.HeadwordId);
            oBase.AddInParameter(oCmd, "@JudicialProcedureId", DbType.String, conditon.JudicialProcedureId);
            oBase.AddInParameter(oCmd, "@PropertyId", DbType.String, conditon.PropertyId);
            oBase.AddInParameter(oCmd, "@CaseIdStart", DbType.String, conditon.CaseIdStart);
            oBase.AddInParameter(oCmd, "@CaseIdEnd", DbType.String, conditon.CaseIdEnd);
            oBase.AddInParameter(oCmd, "@CaseParagraphType", DbType.String, conditon.ParagraphType);
            return Convert.ToInt32(oBase.ExecuteScalar(oCmd));
        }

        public DataTable GetParagraphLogic(DataSourceCondition conditon)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetParagraphLogic");
            oBase.AddInParameter(oCmd, "@HeadwordId", DbType.String, conditon.HeadwordId);
            oBase.AddInParameter(oCmd, "@JudicialProcedureId", DbType.String, conditon.JudicialProcedureId);
            oBase.AddInParameter(oCmd, "@PropertyId", DbType.String, conditon.PropertyId);
            oBase.AddInParameter(oCmd, "@CaseIdStart", DbType.String, conditon.CaseIdStart);
            oBase.AddInParameter(oCmd, "@CaseIdEnd", DbType.String, conditon.CaseIdEnd);

            oBase.AddInParameter(oCmd, "@Court", DbType.String, conditon.Court);
            oBase.AddInParameter(oCmd, "@ParagraphType", DbType.String, conditon.ParagraphType);
            oBase.AddInParameter(oCmd, "@ParagraphkeyWord", DbType.String, conditon.ParagraphkeyWord);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取逻辑段（分页）
        /// </summary>
        /// <param name="conditon">查询条件</param>
        /// <param name="page">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="totalNum">查询总数</param>
        /// <returns></returns>
        public DataTable GetParagraphLogic_Page(DataSourceCondition conditon, int page, int pageSize, out int totalNum)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetParagraphLogic_Page");
            oBase.AddInParameter(oCmd, "@HeadwordId", DbType.String, conditon.HeadwordId);
            oBase.AddInParameter(oCmd, "@JudicialProcedureId", DbType.String, conditon.JudicialProcedureId);
            oBase.AddInParameter(oCmd, "@PropertyId", DbType.String, conditon.PropertyId);
            oBase.AddInParameter(oCmd, "@CaseIdStart", DbType.String, conditon.CaseIdStart);
            oBase.AddInParameter(oCmd, "@CaseIdEnd", DbType.String, conditon.CaseIdEnd);

            oBase.AddInParameter(oCmd, "@Court", DbType.String, conditon.Court);
            oBase.AddInParameter(oCmd, "@ParagraphType", DbType.String, conditon.ParagraphType);
            oBase.AddInParameter(oCmd, "@ParagraphkeyWord", DbType.String, conditon.ParagraphkeyWord);
            oBase.AddInParameter(oCmd, "@page", DbType.Int32, page);
            oBase.AddInParameter(oCmd, "@pageSize", DbType.Int32, pageSize);
            //设置数据库连接超时时间，由于查询数据量较大，需设置较大时间 3分钟
            oCmd.CommandTimeout = 360;
            totalNum = 0;
            var ds = oBase.ExecuteDataSet(oCmd);
            DataTable OtB = ds.Tables[0];

            totalNum = int.Parse(ds.Tables[1].Rows[0][0].ToString());

            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取逻辑段（分页）
        /// </summary>
        /// <param name="conditon">查询条件</param>
        /// <param name="page">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="totalNum">查询总数</param>
        /// <returns></returns>
        public DataTable GetParagraphLogic_PageNew(DataSourceCondition conditon)
        {
           string strSql = string.Format(@"  select top 200000  row_number() over( order by b.CaseId ) as showindex , b.CaseId,b.ParagraphText,c.CourtId,c.PropertyId,b.CaseId from t_Case_HeadWords_Associate AS a  
inner join  t_Case_ParagraphLogic AS b   on a.caseid=b.caseId  join t_Case   as  c   on c.caseid=b.caseId   
where  a.headwordId='{0}' and b.ParagraphTypeId='{1}' and  c.JudicialProcedureId='{2}' ", conditon.HeadwordId,conditon.ParagraphType,conditon.JudicialProcedureId);

            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oCmd.CommandTimeout = 1000;
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }                      
        }

        public string ExceptAdditionalFields(string caseid)
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_ExceptAdditionalFields");
            oBase.AddInParameter(oCmd, "@caseid", DbType.String, caseid);
            return oBase.ExecuteScalar(oCmd).ToString();
        }


        #endregion

        #region 二次加工数据源
        public DataTable GetDataSource_Second(string situation)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetDataSource_Second");
            oBase.AddInParameter(oCmd, "@Situations", DbType.String, situation);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        #endregion


        public DataTable GetDataSource_WrongOrder(string Situations, int Isdelete)//案由ID
        {



            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetDataSource_WrongOrder");
            oBase.AddInParameter(oCmd, "@Situations", DbType.String, Situations);
            oBase.AddInParameter(oCmd, "@Isdelete", DbType.String, Isdelete);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];

            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetDataSourceAllCaseCause()//案由ID
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"  select CC_ID from dbo.SYS_CaseCause
           where CC_IsProcessNode=1");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }


        /// <summary>
        /// 获得案由下所有情形情节
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable GetCauseDataSource(string pid)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetCauseDataSource");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);


            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }

        /// <summary>
        /// 获得案由下所有情形情节
        /// </summary>
        /// <param name="pid">父ID</param>
        /// <param name="purposeId">用途ID</param>
        /// <param name="paragraphId">逻辑段ID</param>
        /// <param name="isCurrentId">是否是当前节点，默认是父节点，取子级</param>
        /// <returns></returns>
        public DataTable GetCauseDataSource(string pid, string purposeId, string paragraphId, bool isCurrentId = false)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);

            string strCcidParams = !isCurrentId ? " and CC_ParentID= '" + pid + "' " : " and cc_id = '" + pid + "' ";
            string strSourceParams = !string.IsNullOrEmpty(purposeId) && !string.IsNullOrEmpty(paragraphId) ?
                " and  Source_ParagraphID = '" + paragraphId + "' and ES_Purpose= '" + purposeId + "' " : " and Source_ID= 'JudgeBook' ";

            string strSql = @"select CC_ID ,CC_Name ,CC_NodeType ,CC_ParentID,keywords,RuleItem,
    (select  CC_IsProcessNode from dbo.SYS_CaseCause where CC_ID=a.CC_ParentID)as PidCC_IsProcessNode,
    (select top 1 CC_RegexInfoCode  from  dbo.SYS_Express_Source where  CC_ID =a.CC_ID " + strSourceParams +
            @") as CC_RegexInfoCode , --某来源信息单元
    (select top 1 substring(Source_ParagraphID,10,20) from  dbo.SYS_Express_Source where  CC_ID =a.CC_ID  
      " + strSourceParams + @") as Source_ParagraphID ,
    (select CC_Name from SYS_CaseCause where CC_ID =a.CC_ParentID ) as  CC_ParentName,--父级名称
	(select  COUNT(*)  from 
     LdtCommonManage.dbo.Reg_InfoRegexRelation  as c
	join 
    LdtCommonManage.dbo.Reg_InfoUnit  as b on c.EI_ID=b.EI_ID
	where EI_Code=(select top 1 CC_RegexInfoCode from  dbo.SYS_Express_Source where  CC_ID =a.CC_ID 
     " + strSourceParams + @")
   ) as RegCount ,--有无正则
       isnull(( select COUNT(*) from  SYS_CaseCause  where CC_ParentID=a.CC_ID  ),0)as   ChildCount --默认下级个数
       ,ElementXML
      from dbo.SYS_CaseCause as a where 1=1 " + strCcidParams + @" order by CC_SerialNumb";


            DbCommand oCmd = oBase.GetSqlStringCommand(strSql);

            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            else
            {
                return new DataTable();
            }


        }



        public DataTable GetAllRelationCC_ID(string Situation)//案由ID
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetAllRelationCC_ID");
            oBase.AddInParameter(oCmd, "@Situation", DbType.String, Situation);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }


        #region 索引ID记录

        public bool Existsluceneid(string id, string CaseCauseID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"select Count(*) from  SYS_Express_ParaLuceneId where  luceneId ='{0}'  and CaseCauseID ='{1}'", id, CaseCauseID);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            if (Convert.ToInt32(oBase.ExecuteScalar(oCmd).ToString()) > 0)
            {
                return true;
            }
            return false;
        }


        public bool UpdateDataState(string id, string CaseCauseID)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"	insert into  SYS_Express_ParaLuceneId   values ( @id ,@CaseCauseID) ");
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                oBase.AddInParameter(oCmd, "@id", DbType.String, id);
                oBase.AddInParameter(oCmd, "@CaseCauseID", DbType.String, CaseCauseID);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }

                return false;
            }
            catch (Exception error)//唯一键
            {
                error.ToString();
                return false;
            }

        }

        #endregion


    }
}
